﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Net;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Net.Mail;

namespace Shifa
{
    public partial class FebsForm : Form
    {
        private Database database;
        private int decimal_places;

        public FebsForm()
        {
            InitializeComponent();

            database = new Database("shifa", "root", "");
            decimal_places = 2;
        }

        private void addMsg(string msg)
        {
            txtStatus.Text += msg + Environment.NewLine;
            txtStatus.Select(txtStatus.Text.Length, 0);
            txtStatus.ScrollToCaret();
        }
        
        private void btnConnect_Click(object sender, EventArgs e)
        {
            if (btnConnect.Text == "Connect")
            {
                database.Server = txtServer.Text;

                if (database.TryConnect())
                {
                    btnConnect.Text = "Disconnect";
                    txtServer.Enabled = false;
                    txtStatus.Text = "Successfully connected to " + database.Server + Environment.NewLine;
                    populateParameters();
                }
                else
                {
                    txtStatus.Text = database.Error + Environment.NewLine;
                }
            }
            else
            {
                addMsg("Disconnected from the database");
                btnConnect.Text = "Connect";
                txtServer.Enabled = true;
                cbYear.Enabled = cbReportBy.Enabled = cbDate.Enabled = btnStart.Enabled = false;
            }
        }

        private void populateParameters()
        {
            addMsg("Getting list of distinct years");
            cbYear.Items.Clear();

            List<string>[] years = database.Select("SELECT DISTINCT class from febs ORDER BY 1", 1);

            for (int i = 0; i < years[0].Count; i++)
            {
                cbYear.Items.Add(years[0][i]);
            }
            
            addMsg("Getting list of distinct clerkships");
            cbReportBy.Items.Clear();

            List<string>[] clerkships = database.Select("SELECT id, name FROM clerkships WHERE id IN (SELECT DISTINCT clerkship FROM febs) ORDER BY 2", 2);

            for (int i = 0; i < clerkships[0].Count; i++)
            {
            	ItemObject iobj = new ItemObject(clerkships[1][i], clerkships[0][i]);
            	cbReportBy.Items.Add(iobj);
            }

            addMsg("Getting list of distinct clerkship dates");
            cbDate.Items.Clear();

            List<string>[] dates = database.Select("SELECT DISTINCT fdate FROM febs ORDER BY 1", 1);

            for (int i = 0; i < dates[0].Count; i++)
            {
            	cbDate.Items.Add(dates[0][i]);
            }

            cbYear.SelectedIndex = 0;
            cbReportBy.SelectedIndex = 0;
            cbDate.SelectedIndex = 0;

            cbYear.Enabled = cbReportBy.Enabled = cbDate.Enabled = btnStart.Enabled = true;
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
        	string c_id = ((ItemObject)(cbReportBy.SelectedItem)).ValueOfKey.ToString();
        	string c_desc = ((ItemObject)(cbReportBy.SelectedItem)).Key.ToString();
        	
        	startClerkshipProcessing(cbYear.SelectedItem.ToString(), c_id, c_desc, cbDate.SelectedItem.ToString());
        }

        private void startClerkshipProcessing(string year, string clerkship_id, string clerkship, string date)
        {
        	addMsg("Starting processing for " + clerkship + ", " + year + ", " + date);

            List<Clerkship> clerkships = new List<Clerkship>();

            string fdate = date;
            string tdate = "";
            getClerkshipData(clerkships, year, fdate, out tdate);
            processClerkshipData(clerkships, clerkship_id, fdate, tdate);
        }

        private void getClerkshipData(List<Clerkship> clerkships, string year, string fdate, out string tdate)
        {
            addMsg("Getting raw data into structures");
            tdate = "";

            string query = "SELECT id, name FROM clerkships WHERE id IN (SELECT DISTINCT clerkship FROM febs WHERE class=" + year + " AND fdate='" + fdate + "') ORDER BY 2";
            List<string>[] clerkships_data = database.Select(query, 2);
            addMsg(query);

            for (int i = 0; i < clerkships_data[0].Count; i++)
            {
                Clerkship clerkship = new Clerkship(clerkships_data[0][i], clerkships_data[1][i], year);

                query = "SELECT DISTINCT teacher FROM febs WHERE clerkship=" + clerkship.Id + " AND class=" + clerkship.Year + " AND fdate='" + fdate + "' ORDER BY 1";
                List<string>[] teachers_data = database.Select(query, 1);
                addMsg(query);

                for (int j = 0; j < teachers_data[0].Count; j++)
                {
                    Teacher teacher = new Teacher(teachers_data[0][j]);

                    query = "SELECT DISTINCT tdate from febs WHERE clerkship=" + clerkship.Id + " AND class=" + clerkship.Year + " AND teacher='" + teacher.Name + "' AND fdate='" + fdate + "'";
                    List<string>[] dates = database.Select(query, 1);
                    tdate = dates[0][0];
                    addMsg(query);
                    
                    query = "SELECT medical_expert1, medical_expert2, communicator1, communicator2, communicator3, communicator4, communicator5, collaborator1, collaborator2, manager1, manager2, health_advocator1, health_advocator2, scholar1, scholar2, scholar3, scholar4, professional1, professional2, professional3, role, comments FROM febs WHERE teacher='" + teacher.Name + "' AND clerkship=" + clerkship.Id + " AND class=" + clerkship.Year + " AND fdate='" + fdate + "'";
                    List<string>[] questions_data = database.Select(query, 22);
                    addMsg(query);
                    addMsg("");

                    Question q_med_exp_1 = new Question("me_1");
                    Question q_med_exp_2 = new Question("me_2");
                    Question q_communi_1 = new Question("cm_1");
                    Question q_communi_2 = new Question("cm_2");
					Question q_communi_3 = new Question("cm_3");
					Question q_communi_4 = new Question("cm_4");
					Question q_communi_5 = new Question("cm_5");
					Question q_collab_1 =  new Question("cl_1");
					Question q_collab_2 =  new Question("cl_2");
					Question q_manage_1 =  new Question("m_1");
					Question q_manage_2 =  new Question("m_2");
					Question q_health_advocate_1 = new Question("ha_1");
                    Question q_health_advocate_2 = new Question("ha_2");
                    Question q_scholar_1 = new Question("s_1");
                    Question q_scholar_2 = new Question("s_2");
					Question q_scholar_3 = new Question("s_3");
					Question q_scholar_4 = new Question("s_4");
					Question q_professiona_1 = new Question("p_1");
					Question q_professional_2 =  new Question("p_2");
					Question q_profesional_3 =  new Question("p_3");
					Question q_role_1 =  new Question("r_1");
					Question q_comment =  new Question("comment", true);



					for (int k = 0; k < questions_data[0].Count; k++)
                    {
						/* Don't consider empty and 0 values */
                    	if (questions_data[0][k] != "" && questions_data[0][k] != "0") q_med_exp_1.Ratings.Add(Convert.ToInt32(questions_data[0][k]));
                        if (questions_data[1][k] != "" && questions_data[1][k] != "0") q_med_exp_2.Ratings.Add(Convert.ToInt32(questions_data[1][k]));
                        if (questions_data[2][k] != "" && questions_data[2][k] != "0") q_communi_1.Ratings.Add(Convert.ToInt32(questions_data[2][k]));
                        if (questions_data[3][k] != "" && questions_data[3][k] != "0") q_communi_2.Ratings.Add(Convert.ToInt32(questions_data[3][k]));
						if (questions_data[4][k] != "" && questions_data[4][k] != "0") q_communi_3.Ratings.Add(Convert.ToInt32(questions_data[4][k]));
						if (questions_data[5][k] != "" && questions_data[5][k] != "0") q_communi_4.Ratings.Add(Convert.ToInt32(questions_data[5][k]));
						if (questions_data[6][k] != "" && questions_data[6][k] != "0") q_communi_5.Ratings.Add(Convert.ToInt32(questions_data[6][k]));
						if (questions_data[7][k] != "" && questions_data[7][k] != "0") q_collab_1.Ratings.Add(Convert.ToInt32(questions_data[7][k]));
						if (questions_data[8][k] != "" && questions_data[8][k] != "0") q_collab_2.Ratings.Add(Convert.ToInt32(questions_data[8][k]));
						if (questions_data[9][k] != "" && questions_data[9][k] != "0") q_manage_1.Ratings.Add(Convert.ToInt32(questions_data[9][k]));
                        if (questions_data[10][k] != "" && questions_data[10][k] != "0") q_manage_2.Ratings.Add(Convert.ToInt32(questions_data[10][k]));
                        if (questions_data[11][k] != "" && questions_data[11][k] != "0") q_health_advocate_1.Ratings.Add(Convert.ToInt32(questions_data[11][k]));
                        if (questions_data[12][k] != "" && questions_data[12][k] != "0") q_health_advocate_2.Ratings.Add(Convert.ToInt32(questions_data[12][k]));
						if (questions_data[13][k] != "" && questions_data[13][k] != "0") q_scholar_1.Ratings.Add(Convert.ToInt32(questions_data[13][k]));
						if (questions_data[14][k] != "" && questions_data[14][k] != "0") q_scholar_2.Ratings.Add(Convert.ToInt32(questions_data[14][k]));
						if (questions_data[15][k] != "" && questions_data[15][k] != "0") q_scholar_3.Ratings.Add(Convert.ToInt32(questions_data[15][k]));
						if (questions_data[16][k] != "" && questions_data[16][k] != "0") q_scholar_4.Ratings.Add(Convert.ToInt32(questions_data[16][k]));
						if (questions_data[17][k] != "" && questions_data[17][k] != "0") q_professiona_1.Ratings.Add(Convert.ToInt32(questions_data[17][k]));
						if (questions_data[18][k] != "" && questions_data[18][k] != "0") q_professional_2.Ratings.Add(Convert.ToInt32(questions_data[18][k]));
						if (questions_data[19][k] != "" && questions_data[19][k] != "0") q_profesional_3.Ratings.Add(Convert.ToInt32(questions_data[19][k]));
						if (questions_data[20][k] != "" && questions_data[20][k] != "0") q_role_1.Ratings.Add(Convert.ToInt32(questions_data[20][k]));
						if (questions_data[21][k] != "") q_comment.Comments.Add(questions_data[21][k]);
                    }

                    teacher.Questions.Add(q_med_exp_1);
                    teacher.Questions.Add(q_med_exp_2);
                    teacher.Questions.Add(q_communi_1);
                    teacher.Questions.Add(q_communi_2);
					teacher.Questions.Add(q_communi_3);
					teacher.Questions.Add(q_communi_4);
					teacher.Questions.Add(q_communi_5);
					teacher.Questions.Add(q_collab_1);
					teacher.Questions.Add(q_collab_2);
					teacher.Questions.Add(q_manage_1);
                    teacher.Questions.Add(q_manage_2);
                    teacher.Questions.Add(q_health_advocate_1);
                    teacher.Questions.Add(q_health_advocate_2);
					teacher.Questions.Add(q_scholar_1);
					teacher.Questions.Add(q_scholar_2);
					teacher.Questions.Add(q_scholar_3);
					teacher.Questions.Add(q_scholar_4);
					teacher.Questions.Add(q_professiona_1);
					teacher.Questions.Add(q_professional_2);
					teacher.Questions.Add(q_profesional_3);
					teacher.Questions.Add(q_role_1);
					teacher.Questions.Add(q_comment);
					
                    clerkship.Teachers.Add(teacher);
                }

                clerkships.Add(clerkship);
            }
        }

        private void processClerkshipData(List<Clerkship> clerkships, string selected_clerkship, string fdate, string tdate)
        {
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            fbd.Description = "Select output folder. This is where the reports will be created.";
            fbd.RootFolder = Environment.SpecialFolder.Desktop;
            fbd.SelectedPath = Environment.GetFolderPath(fbd.RootFolder);
            fbd.ShowNewFolderButton = true;
            
            if (fbd.ShowDialog() == DialogResult.Cancel)
            {
            	MessageBox.Show("Cancelled by user.", "FEBS", MessageBoxButtons.OK, MessageBoxIcon.Information);
            	return;
            }
            string filepath = fbd.SelectedPath + "\\";

            foreach (Clerkship clerkship in clerkships)
            {
            	if (clerkship.Id != selected_clerkship)
            	{
            		continue;
            	}
            	
                addMsg("Creating reports for clerkship [" + clerkship.Name + "]");

                foreach (Teacher teacher in clerkship.Teachers)
                {
                	string filename = clerkship.Name.ToLower() + "-" + clerkship.Year + "-" + teacher.Name.ToLower();
                	addMsg(" " + teacher.Name + " [" + filename + "]");
                    createReport(clerkship, teacher, filepath, filename, fdate, tdate);
                }
            }
            
            MessageBox.Show("Operation completed.", "FEBS", MessageBoxButtons.OK, MessageBoxIcon.Information);
            DialogResult dr = MessageBox.Show("Do you want to send emails automatically now?", "FEBS", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            
            if (dr == DialogResult.No) {
            	System.Diagnostics.Process.Start(filepath);
            } else {
            	sendEmails();
            }
        }

        private void createReport(Clerkship clerkship, Teacher teacher, string filepath, string filename, string fdate, string tdate)
        {
        	string template = File.ReadAllText(Application.StartupPath + "\\febs.html");
        	
        	template = template.Replace("%title%", filename);
        	template = template.Replace("%clerkship%", clerkship.Name);
        	template = template.Replace("%year%", clerkship.Year.ToString());
        	template = template.Replace("%n%", teacher.GetRatingCount().ToString());
        	template = template.Replace("%dates%", fdate + " - " + tdate);
			if (template == null)
				return;
        	template = template.Replace("%teacher%", teacher.Name);

            int npt = teacher.Questions.Count;
            double[] x = new double[npt];
            double[] y1 = new double[npt];
            double[] y2 = new double[npt];
            double[] y3 = new double[npt];
            int i = 0;

        	foreach (Question question in teacher.Questions)
            {
        		if (question.CommentOnly)
        		{
        			template = template.Replace("%comments%", question.GetComments());
        		} else {
	                double min_avg = 0.0;
	                double max_avg = 0.0;
	                clerkship.GetAverages(question, out min_avg, out max_avg);
	
	                template = template.Replace("%h_" + question.Key + "%", Math.Round(max_avg, decimal_places).ToString("F"+decimal_places));
	                template = template.Replace("%a_" + question.Key + "%", Math.Round(question.GetAverage(), decimal_places).ToString("F"+decimal_places));
	                template = template.Replace("%l_" + question.Key + "%", Math.Round(min_avg, decimal_places).ToString("F"+decimal_places));
	
	                x[i] = i+1;
	                y1[i] = Math.Round(max_avg, decimal_places);
	                y2[i] = Math.Round(question.GetAverage(), decimal_places);
	                y3[i] = Math.Round(min_avg, decimal_places);
	
	                i++;
        		}
            }
        	
        	if (teacher.GetRatingCount() < 5)
        	{
        		template = template.Replace("%graph%", "No graph because rating count less than 5.");
        		File.WriteAllText(filepath + filename + ".html", template);
        	} else {
	            template = template.Replace("%graph%", "<img src='./" + filename + ".png'></img>");
	        	File.WriteAllText(filepath + filename + ".html", template);
	
	            NPlot.Windows.PlotSurface2D plotSurface = new NPlot.Windows.PlotSurface2D();
	            plotSurface.Width = 800;
	            plotSurface.Height = 400;
	            plotSurface.Clear();
	
	            NPlot.Grid grid = new NPlot.Grid();
	            grid.HorizontalGridType = NPlot.Grid.GridType.Fine;
	            grid.VerticalGridType = NPlot.Grid.GridType.Fine;
	            plotSurface.Add(grid);
	
	            // create CandlePlot.
	            NPlot.CandlePlot cp = new NPlot.CandlePlot();
	            cp.AbscissaData = x;
	            cp.OpenData = y2;
	            cp.LowData = y3;
	            cp.HighData = y1;
	            cp.CloseData = y2;
	            cp.BearishColor = Color.Red;
	            cp.BullishColor = Color.Green;
	            cp.StickWidth = 5;
	            cp.Color = Color.DarkBlue;
	
	            plotSurface.Add(cp);
	
	            double xmin = 0;
	            double xmax = npt + 1;
	            double ymin = 0;
	            double ymax = 5.1;
	
	            NPlot.LinearAxis linx = (NPlot.LinearAxis)plotSurface.XAxis1;
	            linx.WorldMin = xmin;
	            linx.WorldMax = xmax;
	            linx.AxisColor = Color.Black;
	            linx.LabelColor = Color.Black;
	            linx.TickTextColor = Color.Black;
	            linx.Label = "Questions";
	            plotSurface.XAxis1 = linx;
	
	            NPlot.LinearAxis liny = (NPlot.LinearAxis)plotSurface.YAxis1;
	            liny.WorldMin = ymin;
	            liny.WorldMax = ymax;
	            liny.AxisColor = Color.Black;
	            liny.LabelColor = Color.Black;
	            liny.TickTextColor = Color.Black;
	            liny.Label = "Average Rating";
	            plotSurface.YAxis1 = liny;
	
	            plotSurface.Title = "Graphical Presentation of Average rating by Students";
	            plotSurface.Refresh();
	
	            Bitmap bitmap = new Bitmap(800, 400);
	            plotSurface.DrawToBitmap(bitmap, new Rectangle(0, 0, 800, 400));
	            bitmap.Save(filepath + filename + ".png");
        	}
        }
        
        private void sendEmails()
        {
        	string body = File.ReadAllText(@"C:\Users\Administrator\Desktop\New folder (2)\surgery\surgery-2013-dr. fareeha farooqui.html");
        	sendEmail("rukhshanda.zaman@gmail.com", body);
        }
        
        private void sendEmail(string to, string body)
        {
        	SmtpClient client = new SmtpClient(txtSmtpServer.Text, int.Parse(txtPort.Text));
        	client.Credentials = new NetworkCredential(txtEmail.Text, "Evaluation786");
        	
        	MailMessage message = new MailMessage();
        	
        	message.From = new MailAddress(txtEmail.Text);
        	message.To.Add(to);
        	message.Subject = "Clerkship feedback";
        	
        	message.IsBodyHtml = true;
        	message.Body = body;
        	
        	Attachment attachment = new Attachment(@"C:\Users\Administrator\Desktop\New folder (2)\surgery\surgery-2013-dr. fareeha farooqui.png");
        	message.Attachments.Add(attachment);
        	
        	client.Send(message);
        }
    }
}
